#!/usr/bin/python
#
# Author: Paul D. Eden <paul@benchline.org>
# Created: 2008-09-30

import db_row

class EdenDB(object):
    """
    A thin db2 api wrapper
    """

    def __init__(self, conn, verbose=False):
        """
        >>> import sqlite3
        >>> conn = sqlite3.connect(":memory:")
        >>> db = EdenDB(conn)
        """
        self.conn = conn
        self.verbose = verbose

    ##########  Private Methods  ##########

    def _sql_query(self, sql, values=None, iter=False):
        """
        >>> result = db._sql_query("select count(*) as cnt from test;")
        >>> type(result[0][0]) is int
        True
        >>> result = db._sql_query("select count(*) as cnt from test;", iter=True)
        >>> for row in result:
        ...     pass
        >>> type(row.fields.cnt) is int
        True
        >>> result = db._sql_query("select count(*) as cnt from test where age = ?;", values=[1])
        >>> result[0]['cnt'] == 1
        True
        >>> result[0].fields.cnt == 1
        True
        >>> db.verbose = True
        >>> result = db._sql_query("select count(*) as cnt from test where age = ?;", values=[1])
        Executing (select count(*) as cnt from test where age = ?;) with ([1]) as values...
        >>> db.verbose = False
        """
        if self.verbose:
            print "Executing (%s) with (%s) as values..." % (sql, str(values))
        cur = self.conn.cursor()
        if not values:
            cur.execute(sql)
        else:
            cur.execute(sql, values)
        if sql.strip().lower().startswith('select'): # only return anything if select statement
            R = db_row.IMetaRow(cur.description)
            if iter:
                return self._row_generator(cur, R)
            else:
                result_obj_list = [R(row) for row in cur.fetchall()]
                cur.close()
                return result_obj_list

    def _row_generator(self, cur, R):
        row = cur.fetchone()
        while row:
            yield R(row)
            row = cur.fetchone()
        cur.close()

    ##########  Public Methods  ##########

    def dml(self, sql):
        """
        For use with Data Manipulation SQL statements
        like create, drop, grant, etc

        >>> db.dml("create table test2 (a int, b varchar)")
        >>> db.dml("insert into test2 (a,b) values (1,'2')")
        >>> db.select_one_value('a', 'test2')
        1
        >>> db.dml("drop table test2")
        """
        self._sql_query(sql)
        self.conn.commit()

    def insert(self, tablename="", columns="", values="", sql=""):
        """
        >>> db.insert('test', ['name', 'age'], ['peter', '33'])
        >>> db.insert(sql="insert into test (name, age) values ('dan', 100)")
        """
        if not sql:
            assert tablename and columns and values
            sql = "insert into %s (%s) values (%s);" % (tablename,
                ",".join(columns), ",".join("?"*len(values)))
        self._sql_query(sql, values)
        self.conn.commit()

    def update(self, tablename="", columns="", values="", where="", sql=""):
        """
        >>> db.update('test', ['name'], ['peter'], 'age = 3')
        >>> db.update(sql="update test set name = 'dave' where age = 2")
        """
        if not sql:
            assert tablename and columns and values
            if where:
                where = " where " + where
            sql = "update %s set %s%s;" % (tablename,
                ",".join(["%s=?" % c for c in columns]), where)
        self._sql_query(sql, values)
        self.conn.commit()

    def delete(self, tablename="", where="", sql=""):
        """
        Make sure in these tests to not delete rows
        used in other tests

        >>> db.delete('test', 'age = 4')
        >>> db.delete(sql="delete from test where age = 5")
        >>> db.select(['*'], 'test', 'age in (4, 5)')
        []
        """
        if not sql:
            assert tablename
            if where:
                where = " where " + where
            sql = "delete from %s%s;" % (tablename, where)
        self._sql_query(sql)
        self.conn.commit()

    def select(self, columns="", tablename="", where="", orderby="", sql="", iter=False):
        """
        >>> db.select(['*'], 'test', 'age = 7', orderby='age')
        [(u'paul7', 7)]
        >>> result = db.select(sql="select * from test where age = 7")
        >>> result
        [(u'paul7', 7)]
        >>> result[0]['name'] == 'paul7'
        True
        >>> result[0].fields.age == 7
        True
        """
        if not sql:
            assert tablename and columns
            if where:
                where = " where " + where
            if orderby:
                orderby = " order by " + orderby
            sql = "select %s from %s%s%s" % (",".join(columns), tablename, where, orderby)
        return self._sql_query(sql, iter)

    def select_one_row(self, columns="", tablename="", where="", sql=""):
        """
        >>> db.select_one_row(['*'], 'test', where="name='paul8'")
        (u'paul8', 8)
        >>> db.select_one_row(sql="select age from test where name = 'paul9'")
        (9,)
        >>> db.select_one_row(sql="select age from test where name = 'paul100'")
        []
        """
        if not sql:
            assert tablename and columns
            rvalue = self.select(columns, tablename, where)
        else:
            rvalue = self.select(sql=sql)
        if rvalue:
            return rvalue[0]
        else:
            return []

    def select_one_value(self, column="", tablename="", where="", sql=""):
        """
        >>> db.select_one_value('name', 'test', 'age = 8')
        u'paul8'
        >>> db.select_one_value(sql="select name from test where age = 8")
        u'paul8'
        >>> db.select_one_value(sql="select name from test where age = 8378")
        ''
        """
        if not sql:
            assert tablename and column
            rvalue = self.select([column], tablename, where)
        else:
            rvalue = self.select(sql=sql)
        if rvalue:
            return rvalue[0][0]
        else:
            return ""

